package com.situ.crm.test;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;

import javax.sound.midi.Soundbank;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

public class POITest2 {

    @Test
    public void  testWrite03Excel() throws Exception {
        // 1. 创建工作簿:Workboot
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 2. 创建工作表：Sheet
        HSSFSheet sheet = workbook.createSheet("Hello POI");
        // 3. 创建行：Row
        HSSFRow row = sheet.createRow(2);
        // 4. 创建单元格：Cell
        HSSFCell cell = row.createCell(2);
        cell.setCellValue("Hello POI");
        // 输出Excel到文件
        FileOutputStream outputStream = new FileOutputStream("d:\\poitest\\工作簿1.xls");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }


    @Test
    public void testRead07Excel() throws Exception {
        FileInputStream fileInputStream = new FileInputStream("D:\\poitest\\工作簿2.xlsx");
        // 1. 创建工作簿:Workboot
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        // 2. 创建工作表：Sheet
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 3. 创建行:Row
        XSSFRow row = sheet.getRow(2);
        // 4. 创建单元格：Cell
        XSSFCell cell = row.getCell(2);
        String value = cell.getStringCellValue();
        System.out.println(value);

        workbook.close();
        fileInputStream.close();
    }

    @Test
    public void testWrite07ExcelStyle() throws Exception {
        // 1. 创建工作簿:Workboot
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 2. 创建工作表：Sheet
        XSSFSheet sheet = workbook.createSheet("用户列表");
        // 设置默认的列宽
        sheet.setDefaultColumnWidth(25);
        // 创建合并单元格对象
        CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 6, 1, 6);
        sheet.addMergedRegion(cellRangeAddress);
        // 3. 创建行:Row
        XSSFRow row = sheet.createRow(2);
        // 4. 创建单元格：Cell
        XSSFCell cell = row.createCell(1);
        cell.setCellValue("用户列表");
        XSSFCellStyle style = createStyle(workbook, 15);
        cell.setCellStyle(style);

        // 4. 创建列 id，name，age， gender
        XSSFRow rowHead = sheet.createRow(7);
        String[] array = {"编号", "用户名", "手机号", "邮箱"};
        for (int i = 0; i < array.length; i++) {
            XSSFCell cellHead = rowHead.createCell(i + 1);
            XSSFCellStyle headStyle = createStyle(workbook, 10);
            cellHead.setCellStyle(headStyle);
            cellHead.setCellValue(array[i]);
        }

        // 输出Excel到文件
        FileOutputStream outputStream = new FileOutputStream("d:\\poitest\\工作簿3.xlsx");
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

    private XSSFCellStyle createStyle(XSSFWorkbook workbook, int fontSize) {
        // 创建单元格的样式
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        // 创建字体
        XSSFFont font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
        font.setFontHeightInPoints((short) fontSize);// 设置字体大小
        style.setFont(font);
        return style;
    }
}
